package com.translate;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRst;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Pattern;

/**
 * @author Administrator
 */
@Slf4j
public class Excel2007Reader extends DefaultHandler {

    private IRowReader rowReader = new RowReader();

    /**
     * 共享字符串表
     */
    private SharedStringsTable sst;

    /**
     * 上一次的内容
     */
    private String lastContents;

    /**
     * 字符串标识
     */
    private boolean nextIsString;

    /**
     * 工作表索引
     */
    private int sheetIndex = -1;

    /**
     * 行集合
     */
    private List<String> rowlist = new ArrayList<String>();
    ;

    /**
     * 当前行
     */
    private int curRow = 0;



    /**
     * 当前列
     */
    private int curCol = 0;

    /**
     * T元素标识
     */
    private boolean isTElement;

    /**
     * 异常信息，如果为空则表示没有异常
     */
    private String exceptionMessage;

    /**
     * 单元格数据类型，默认为字符串类型
     */
    private CellDataType nextDataType = CellDataType.SSTINDEX;

    private final DataFormatter formatter = new DataFormatter();

    private short formatIndex;

    private String formatString;

    /**
     * 定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
     */
    private String preRef = null, ref = null;

    /**
     * 定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
     */
    private String maxRef = null;

    /**
     * 起始单元格
     */
    private String startRef = null;

    /**
     * 单元格
     */
    private StylesTable stylesTable;

    private String tableName;


    public static final String CELL = "c";
    public static final String VALUE = "r";
    public static final String ELEMENT = "t";
    public static final String TYPE = "s";
    public static final String BOOL = "b";
    public static final String ERROR = "e";
    public static final String INLINESTR = "inlineStr";
    public static final String FORMULA = "str";
    public static final String DATE = "m/d/yy";
    public static final String DATE1 = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy";
    public static final String ROW = "row";
    public static final String INDEX = "v";
    public static Integer maxCol=0;

    /**
     * 遍历工作簿中所有的电子表格
     *
     * @param filename ""
     * @throws IOException        ""
     * @throws OpenXML4JException ""
     * @throws SAXException       ""
     * @throws Exception          ""
     */
    public void process(String filename) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = this.fetchSheetParser(sst);
        Iterator<InputStream> sheets = xssfReader.getSheetsData();
        while (sheets.hasNext()) {
            curRow = 0;
            sheetIndex++;
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
        }
    }

    public void process(String fileName, String tableName) throws IOException, OpenXML4JException, SAXException {
        this.tableName = tableName;
        OPCPackage pkg = OPCPackage.open(fileName);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = this.fetchSheetParser(sst);

        InputStream sheet = xssfReader.getSheet("rId1");
        sheetIndex++;
        InputSource sheetSource = new InputSource(sheet);
        parser.parse(sheetSource);
        sheet.close();
    }

    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        this.sst = sst;
        parser.setContentHandler(this);
        return parser;
    }

    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        // c => 单元格
        if (CELL.equals(name)) {
            // 当前单元格的位置
            ref = attributes.getValue(VALUE);
            // 设定单元格类型
            this.setNextDataType(attributes);
            String cellType = attributes.getValue(ELEMENT);
            if (cellType != null && TYPE.equals(cellType)) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        }
        // 当元素为t时
        if (ELEMENT.equals(name)) {
            isTElement = true;
        } else {
            isTElement = false;
        }
        // 置空
        lastContents = "";
    }

    /**
     * 单元格中的数据可能的数据类型
     */
    enum CellDataType {
        /**
         * 布尔
         */
        BOOL,
        /**
         * error
         */
        ERROR,
        /**
         *
         */
        FORMULA,
        /**
         *
         */
        INLINESTR,
        /**
         *
         */
        SSTINDEX,
        /**
         * 数字
         */
        NUMBER,
        /**
         * 日期
         */
        DATE,
        /**
         * 空
         */
        NULL
    }

    /**
     * 处理数据类型
     *
     * @param attributes ""
     */
    public void setNextDataType(Attributes attributes) {
        nextDataType = CellDataType.NUMBER;
        formatIndex = -1;
        formatString = null;
        String cellType = attributes.getValue(ELEMENT);
        String cellStyleStr = attributes.getValue(TYPE);

        if (BOOL.equals(cellType)) {
            nextDataType = CellDataType.BOOL;
        } else if (ERROR.equals(cellType)) {
            nextDataType = CellDataType.ERROR;
        } else if (INLINESTR.equals(cellType)) {
            nextDataType = CellDataType.INLINESTR;
        } else if (TYPE.equals(cellType)) {
            nextDataType = CellDataType.SSTINDEX;
        } else if (FORMULA.equals(cellType)) {
            nextDataType = CellDataType.FORMULA;
        }

        if (cellStyleStr != null) {
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            formatIndex = style.getDataFormat();
            formatString = style.getDataFormatString();

            if (DATE.equals(formatString)) {
                nextDataType = CellDataType.DATE;
                formatString = "yyyy-MM-dd hh:mm:ss";
            } else if (DATE1.equals(formatString)) {
                nextDataType = CellDataType.DATE;
                formatString = "yyyy-MM-dd hh:mm:ss";
            }

            if (formatString == null) {
                nextDataType = CellDataType.NULL;
                formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
            }
        }
    }

    /**
     * 对解析出来的数据进行类型处理
     *
     * @param value   ""
     *                单元格的值（这时候是一串数字）
     * @param thisStr ""
     *                一个空字符串
     * @return ""
     */
    @SuppressWarnings("deprecation")
    public String getDataValue(String value, String thisStr) {
        switch (nextDataType) {
            // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
            case BOOL:
                char first = value.charAt(0);
                thisStr = first == '0' ? "FALSE" : "TRUE";
                break;
            case ERROR:
                thisStr = "\"ERROR:" + value.toString() + '"';
                break;
            case FORMULA:
                thisStr = '"' + value.toString() + '"';
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                thisStr = rtsi.toString();
                rtsi = null;
                break;
            case SSTINDEX:
                try {
                    XSSFRichTextString rtss = new XSSFRichTextString(value.toString());
                    thisStr = rtss.toString();
                    rtss = null;
                } catch (Exception ex) {
                    thisStr = value.toString();
                }
                break;
            case NUMBER:
                if (formatString != null) {
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
                } else {
                    thisStr = value;
                }
                thisStr = thisStr.replace("_", "").trim();
                break;
            case DATE:
                try {
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
                } catch (NumberFormatException ex) {
                    thisStr = value.toString();
                }
                break;
            default:
                thisStr = " ";

                break;
        }
        return thisStr;
    }

    @Override
    public void endElement(String uri, String localName, String name) throws SAXException {
        int culNum=0;
        if(ref!=null) {
            final String[] split = ref.split("|");
            for (String s : split) {
                if (!StringUtils.isNumber(s)) {
                    culNum += s.hashCode();
                }
            }
        }
        // 根据SST的索引值的到单元格的真正要存储的字符串
        // 这时characters()方法可能会被调用多次
        if (nextIsString && StringUtils.isNotEmpty(lastContents) && StringUtils.isNumber(lastContents)&&!"c".equals(localName)) {
            try {
                int idx = Integer.parseInt(lastContents);
                final CTRst entryAt = sst.getEntryAt(idx);
                lastContents = new XSSFRichTextString(entryAt).toString();
            } catch (Exception ex) {
                log.error("",ex);
            }
        }

        // t元素也包含字符串
        if (isTElement) {
            // 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
            String value = lastContents.trim();
            rowlist.add(curCol, value);
            curCol++;
            isTElement = false;
        } else if (INDEX.equals(name)) {
            if (maxRef != null) {
                //超过切割点不再读取数据  max AS1   AZ1不再读取，AZ2不再读取，B2读取
                if(curRow>0&&culNum>maxCol){
                    return;
                }
            }
            // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
            String value = this.getDataValue(lastContents.trim(), "");
            if (startRef == null) {
                startRef = ref;
            } else if (org.apache.commons.lang.StringUtils.isBlank(value)) {
                //判断是否是第一行 依据 与开始单元格后面数字同行
                String regEx="[^0-9]";
                final Pattern compile = Pattern.compile(regEx);
                final String s = compile.matcher(ref).replaceAll("").trim();
                if ( "1".equals(s)) {
                    maxRef = ref;
                    maxCol=0;
                    final String[] split = maxRef.split("|");
                    for (String s1 : split) {
                        if (!StringUtils.isNumber(s1)) {
                            maxCol += s1.hashCode();
                        }
                    }
                    return;
                }
            } else {
                String regEx="[^0-9]";
                final Pattern compile = Pattern.compile(regEx);
                final String s = compile.matcher(ref).replaceAll("").trim();
                if ( "1".equals(s)) {
                    int len = countNullCell(ref, maxRef);
                    if (len > 0 && maxRef != null) {
                        return;
                    }
                    maxRef = ref;
                    maxCol=0;
                    final String[] split = maxRef.split("|");
                    for (String s1 : split) {
                        if (!StringUtils.isNumber(s1)) {
                            maxCol += s1.hashCode();
                        }
                    }
                }

            }
            // 补全单元格之间的空单元格
            if (!ref.equals(preRef)) {
                int len = countNullCell(ref, preRef);
                for (int i = 0; i < len; i++) {
                    rowlist.add(curCol, "");
                    curCol++;
                }
            }

            rowlist.add(curCol, value);
            preRef = ref;
            curCol++;
        } else {
            // 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
            if (ROW.equals(name)) {
                // 默认第一行为表头，以该行单元格数目为最大数目
                if (curRow != 0) {
                    // 补全一行尾部可能缺失的单元格
                    if (maxRef != null) {
                        int len = countNullCell(maxRef, preRef);
                        for (int i = 0; i <= len; i++) {
                            rowlist.add(curCol, "");
                            curCol++;
                        }
                    }
                }
                rowReader.getRows(sheetIndex, curRow, rowlist, tableName);
                rowlist.clear();
                curRow++;
                curCol = 0;
                preRef = null;
                ref = null;
            }
        }
    }

    /**
     * 计算两个单元格之间的单元格数目(同一行)
     *
     * @param ref    ""
     * @param preRef ""
     * @return ""
     */
    public int countNullCell(String ref, String preRef) {
        // excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
        String xfd = ref.replaceAll("\\d+", "");
        String xfd1 = preRef == null ? "@" : preRef.replaceAll("\\d+", "");

        xfd = fillChar(xfd, 3, '@', true);
        xfd1 = fillChar(xfd1, 3, '@', true);

        char[] letter = xfd.toCharArray();
        char[] letter1 = xfd1.toCharArray();
        int res = (letter[0] - letter1[0]) * 26 * 26 + (letter[1] - letter1[1]) * 26 + (letter[2] - letter1[2]);
        return res - 1;
    }

    /**
     * 字符串的填充
     *
     * @param str   ""
     * @param len   ""
     * @param let   ""
     * @param isPre ""
     * @return ""
     */
    String fillChar(String str, int len, char let, boolean isPre) {
        int len1 = str.length();
        if (len1 < len) {
            if (isPre) {
                for (int i = 0; i < (len - len1); i++) {
                    str = let + str;
                }
            } else {
                for (int i = 0; i < (len - len1); i++) {
                    str = str + let;
                }
            }
        }
        return str;
    }

    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        // 得到单元格内容的值
        lastContents  +=new String(ch, start, length);
    }

    /**
     * @return the exceptionMessage
     */
    public String getExceptionMessage() {
        return exceptionMessage;
    }
}
